package com.industrial.operation.service.impl;

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import com.industrial.common.core.utils.DateUtils;
import com.industrial.common.core.web.domain.AjaxResult;
import com.industrial.common.security.utils.DictUtils;
import com.industrial.common.security.utils.SecurityUtils;
import com.industrial.operation.domain.gxhzb.*;
import com.industrial.operation.mapper.OperationIndexManagerMapper;
import com.industrial.operation.mapper.OperationKfqScoreValueMapper;
import io.swagger.models.auth.In;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Service;
import com.industrial.operation.mapper.OperationKfqInfoMapper;

import com.industrial.operation.service.IOperationKfqInfoService;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 工业运行评价-开发区信息Service业务层处理
 *
 * @author SangGuangCan
 * @date 2024-02-20
 */
@Service
public class OperationKfqInfoServiceImpl implements IOperationKfqInfoService
{
    @Autowired
    private OperationKfqInfoMapper operationKfqInfoMapper;

    @Autowired
    private OperationIndexManagerMapper operationIndexManagerMapper;

    @Autowired
    private OperationKfqScoreValueMapper operationKfqScoreValueMapper;

    @Autowired
    private StringRedisTemplate redisTemplate;

    /**
     * 查询工业运行评价-开发区信息
     *
     * @param baseId 工业运行评价-开发区信息主键
     * @return 工业运行评价-开发区信息
     */
    @Override
    public OperationKfqInfo selectOperationKfqInfoByBaseId(String baseId)
    {
        OperationKfqInfo operationKfqInfo = operationKfqInfoMapper.selectOperationKfqInfoByBaseId(baseId);
        OperationKfqScoreValue params = new OperationKfqScoreValue();
        params.setPkId(baseId);
        List<Map<String,Object>> scoreValues = operationKfqScoreValueMapper.selectOperationKfqScoreValueByPkId(params);
        operationKfqInfo.setScoreValueList(scoreValues);
        return operationKfqInfo;
    }

    /**
     * 查询工业运行评价-开发区信息列表
     *
     * @param operationKfqInfo 工业运行评价-开发区信息
     * @return 工业运行评价-开发区信息
     */
    @Override
    public List<OperationKfqInfo> selectOperationKfqInfoList(OperationKfqInfo operationKfqInfo)
    {
        List<OperationKfqInfo> operationKfqInfos = operationKfqInfoMapper.selectOperationKfqInfoList(operationKfqInfo);
        for(OperationKfqInfo temp : operationKfqInfos){
            String baseId = temp.getBaseId();
            OperationKfqScoreValue params = new OperationKfqScoreValue();
            params.setPkId(baseId);
            List<Map<String,Object>> scoreValues = operationKfqScoreValueMapper.selectOperationKfqScoreValueByPkId(params);
            if(scoreValues != null && !scoreValues.isEmpty()){
                BigDecimal bigDecimal = scoreValues.stream().map(k -> {
                    String score = (String) k.get("score");
                    if (StringUtils.isBlank(score)) {
                        return BigDecimal.ZERO;
                    } else {
                        return new BigDecimal(score);
                    }
                }).reduce(BigDecimal::add).get();
                temp.setZf(bigDecimal);
            }else{
                temp.setZf(BigDecimal.ZERO);
            }
            temp.setScoreValueList(scoreValues);
        }
        return operationKfqInfos;
    }

    /**
     * 新增工业运行评价-开发区信息
     *
     * @param operationKfqInfo 工业运行评价-开发区信息
     * @return 结果
     */
    @Override
    public int insertOperationKfqInfo(OperationKfqInfo operationKfqInfo)
    {
        return operationKfqInfoMapper.insertOperationKfqInfo(operationKfqInfo);
    }

    /**
     * 修改工业运行评价-开发区信息
     *
     * @param operationKfqInfo 工业运行评价-开发区信息
     * @return 结果
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public int updateOperationKfqInfo(OperationKfqInfo operationKfqInfo)
    {
        List<Map<String, Object>> scoreValueList = operationKfqInfo.getScoreValueList();
        List<OperationKfqScoreValue> updateList = new ArrayList<>();
        for(Map<String, Object> item : scoreValueList){
            OperationKfqScoreValue temp = new OperationKfqScoreValue();
            temp.setBaseId((String) item.get("base_id"));
            temp.setBaseId((String) item.get("base_id"));
            temp.setGroupOrder((String) item.get("groupOrder"));
            temp.setiValue((String) item.get("value"));
            updateList.add(temp);
        }

        if(!updateList.isEmpty()){
            operationKfqScoreValueMapper.batchUpdateById(updateList);
        }
        return operationKfqInfoMapper.updateOperationKfqInfo(operationKfqInfo);
    }

    /**
     * 批量删除工业运行评价-开发区信息
     *
     * @param baseIds 需要删除的工业运行评价-开发区信息主键
     * @return 结果
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public int deleteOperationKfqInfoByBaseIds(String[] baseIds)
    {
        if (baseIds.length > 0){
            operationKfqScoreValueMapper.deleteOperationKfqInfoByPkId(baseIds);
        }
        return operationKfqInfoMapper.deleteOperationKfqInfoByBaseIds(baseIds);
    }

    /**
     * 删除工业运行评价-开发区信息信息
     *
     * @param baseId 工业运行评价-开发区信息主键
     * @return 结果
     */
    @Override
    public int deleteOperationKfqInfoByBaseId(String baseId)
    {
        return operationKfqInfoMapper.deleteOperationKfqInfoByBaseId(baseId);
    }

    /**
     * 查询表的标头信息
     *
     * @param params 查询参数
     * @return
     */
    @Override
    public List<Map<String, String>> queryTableHeader(OperationIndexManager params) {
        List<OperationIndexManager> operationIndexManagers = operationIndexManagerMapper.selectOperationIndexManagerList(params);
        String years = params.getYears();
        if(operationIndexManagers == null || operationIndexManagers.isEmpty()){
            throw new RuntimeException(String.format("请先设置%s年的个性指标数据", years));
        }
        List<Map<String, String>> list = new ArrayList<>();
        for(OperationIndexManager temp : operationIndexManagers){
            Map<String, String> tempMap = new HashMap<>(16);
            String indexName = temp.getIndexName();
            tempMap.put("indexName",indexName);
            if(!StringUtils.equals(indexName,"亩均税收及增幅")){
                // empMap.put("indexNameScore", StringUtils.isBlank(indexName) ? "" : indexName + "分数");
                tempMap.put("indexNameScore", StringUtils.isBlank(indexName) ? "" : indexName + "得分");
                //tempMap.put("indexNameValue",StringUtils.isBlank(indexName) ? "" : indexName + "分值");
                tempMap.put("indexNameValue",StringUtils.isBlank(indexName) ? "" : indexName + "(%)");
                tempMap.put("indexNameOrder",StringUtils.isBlank(indexName) ? "" : indexName + "排名");
            }else{
                // empMap.put("indexNameScore", StringUtils.isBlank(indexName) ? "" : indexName + "分数");
                tempMap.put("indexNameScore", StringUtils.isBlank(indexName) ? "" : indexName + "得分");
                //tempMap.put("indexNameValue",StringUtils.isBlank(indexName) ? "" : indexName + "分值");
                tempMap.put("indexNameValue",StringUtils.isBlank(indexName) ? "" : indexName + (StrUtil.equals(years, "2024") ? "(万元/亩)" : "(元/亩)"));
                tempMap.put("indexNameOrder",StringUtils.isBlank(indexName) ? "" : indexName + "排名");
            }

            list.add(tempMap);
        }
        return list;
    }

    @Override
    public Workbook createTemplateWb(OperationIndexManager params) {
        List<OperationIndexManager> operationIndexManagers = operationIndexManagerMapper.selectOperationIndexManagerList(params);
        Workbook wb = new SXSSFWorkbook(500);
        XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle();
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setWrapText(true);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(180,198,231)));
        SXSSFSheet sheet = ((SXSSFWorkbook) wb).createSheet("开发区信息");

        //创建标头行
        SXSSFRow row = sheet.createRow(0);

        //创建固定的单元格
        SXSSFCell khqjCell = row.createCell(0, CellType.STRING);
        SXSSFCell kfqNameCell = row.createCell(1, CellType.STRING);
        SXSSFCell cityCell = row.createCell(2, CellType.STRING);
        SXSSFCell groupCell = row.createCell(3, CellType.STRING);



        khqjCell.setCellStyle(headerStyle);
        kfqNameCell.setCellStyle(headerStyle);
        cityCell.setCellStyle(headerStyle);
        groupCell.setCellStyle(headerStyle);

        khqjCell.setCellValue("考核期间");
        kfqNameCell.setCellValue("开发区名称");
        cityCell.setCellValue("归属地市");
        groupCell.setCellValue("归属组别");

        for(int i = 0;i< operationIndexManagers.size();i++){
            OperationIndexManager operationIndexManager = operationIndexManagers.get(i);
            String indexName = operationIndexManager.getIndexName();


            if(StringUtils.equals(params.getRemark(),"分值")){
                SXSSFCell tempCell = row.createCell(i + 4, CellType.STRING);
                tempCell.setCellStyle(headerStyle);
                tempCell.setCellValue(indexName + "分值");
            }else if(StringUtils.equals(params.getRemark(),"分数")){
                SXSSFCell tempCell = row.createCell(i + 4 + i, CellType.STRING);
                tempCell.setCellValue(indexName + "分数");
                SXSSFCell tempOrderCell = row.createCell(i + 5 + i, CellType.STRING);
                tempOrderCell.setCellValue(indexName + "排名");
            }
        }



        if(StringUtils.equals(params.getRemark(),"分数")){
            short lastCellNum1 = row.getLastCellNum();
            SXSSFCell addScoreNameCell = row.createCell(lastCellNum1, CellType.STRING);
            SXSSFCell addScoreValueCell = row.createCell(lastCellNum1 + 1, CellType.STRING);
            addScoreNameCell.setCellValue("加分项");
            addScoreValueCell.setCellValue("加分项分值");
        }

        short lastCellNum = row.getLastCellNum();
        for(short j = 0; j <lastCellNum; j++){
            sheet.setColumnWidth((int)j,500*11);
        }
        String[] attr = new String[]{"第一季度","第二季度","第三季度","第四季度","全年"};
        setHssfValidation(sheet,attr, 1, 500, 0, 0);

        String[] groupArgs = new String[]{"第一组","第二组","第三组"};
        setHssfValidation(sheet,groupArgs, 1, 500, 5, 5);
        return wb;
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    public static Sheet setHssfValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
                                          int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
        return sheet;
    }


    /**
     * <p>
     * 导入值
     * </p>
     *
     * @param file
     * @param years
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importExcelTemplateValue(MultipartFile file, String years) {
        try {

            //查询出指标对应的ID
            OperationIndexManager params = new OperationIndexManager();
            params.setYears(years);
            List<OperationIndexManager> operationIndexManagers = operationIndexManagerMapper.selectOperationIndexManagerList(params);

            if(operationIndexManagers == null){
                throw new RuntimeException("没有查询到当年的指标");
            }

            Workbook wb = WorkbookFactory.create(file.getInputStream());
            Sheet sheetAt = wb.getSheetAt(0);
            if(sheetAt == null){
                throw new RuntimeException("未读取到数据");
            }

            List<KfqInfo> result = operationIndexManagerMapper.loadAllKfqInfo();
            if(result == null || result.isEmpty()){
                throw new RuntimeException("没有找到开发区信息");
            }

            List<KfqInfo> cityArgs = operationIndexManagerMapper.selectAllCity();
            if(cityArgs == null || cityArgs.isEmpty()){
                throw new RuntimeException("没有找到地市信息");
            }
            Map<String, String> cityInfoMap = cityArgs.stream().collect(Collectors.toMap(KfqInfo::getName, KfqInfo::getCode));
            Map<String, String> kfqInfoMap = result.stream().collect(Collectors.toMap(KfqInfo::getName, KfqInfo::getCode));
            //读出Sheet的标头
            Row headRow = sheetAt.getRow(0);
            //读出最后一个单元格
            short headLastCellNum = headRow.getLastCellNum();

            //标头的索引对应的标头文字
            Map<String,String> indexToTitleMapping = new HashMap<>(16);

            for(short i = 0;i < headLastCellNum;i++){
                Cell readCell = headRow.getCell((int) i);
                String readCellValue = readCell.getStringCellValue();
                indexToTitleMapping.put(String.valueOf(i),readCellValue);
            }

            List<Map<String,Object>> sroceList = new ArrayList<>();

            //有多少行的数据
            int lastRowNum = sheetAt.getLastRowNum();
            for(int i =1;i<=lastRowNum;i++){
                Row tempRow = sheetAt.getRow(i);
                Map<String,Object> temp = new HashMap<>();
                for(short j = 0;j < headLastCellNum;j++){
                    Cell cell = tempRow.getCell(j);
                    String cellType = cell.getCellType().name();
                    if(StringUtils.equals(cellType,"STRING")){
                        temp.put(indexToTitleMapping.get(String.valueOf(j)),cell.getStringCellValue());
                    }else if(StringUtils.equals(cellType,"NUMERIC")){
                        temp.put(indexToTitleMapping.get(String.valueOf(j)),cell.getNumericCellValue());
                    }
                }
                sroceList.add(temp);
            }

            Map<String, String> indexNameToIdMapping = operationIndexManagers.stream().collect(Collectors.toMap(k -> k.getIndexName() + "分值", OperationIndexManager::getBaseId));

            if(sroceList.isEmpty()){
                throw new RuntimeException("没有找到文件中的数据");
            }

            Map<String, Object> xlsxZeroData = sroceList.get(0);

            //查询当前年份导入的开发区信息
            String khqjSt = (String)xlsxZeroData.get("考核期间");
            String gszbSt = (String)xlsxZeroData.get("归属组别");

            String khqjZd = DictUtils.getDictValue("kfq_khqj", (String) khqjSt);
            String zbZd = DictUtils.getDictValue("kfq_group", (String) gszbSt);

            OperationKfqInfo kfqInfoParams = new OperationKfqInfo();
            kfqInfoParams.setYears(years);
            kfqInfoParams.setDap(khqjZd);
            kfqInfoParams.setGroupCode(zbZd);

            List<OperationKfqInfo> operationKfqInfos = operationKfqInfoMapper.selectOperationKfqInfoList(kfqInfoParams);
            Map<String, String> existKfq = new HashMap<>();
            if(operationKfqInfos != null && !operationKfqInfos.isEmpty()){
                existKfq = operationKfqInfos.stream().collect(Collectors.toMap(OperationKfqInfo::getDzn, OperationKfqInfo::getBaseId));
            }


            //构建保存到数据库中的数据格式
            List<OperationKfqInfo> mainInfoTable = new ArrayList<>();
            List<OperationKfqScoreValue> scoreValuesList = new ArrayList<>();
            List<OperationKfqScoreValue> updateScoreValuesList = new ArrayList<>();

            StringBuilder errorMsg = new StringBuilder();

            for(Map<String,Object> item : sroceList){

                String kfpNameTemp = (String)item.get("开发区名称");
                if(existKfq.containsKey(kfpNameTemp)){
                    String baseId = existKfq.get(kfpNameTemp);
                    for(Map.Entry<String,Object> t : item.entrySet()){
                        String key = t.getKey();
                        Object value = t.getValue();
                        if(!(StringUtils.equals(key,"考核期间")
                                || StringUtils.equals(key,"开发区名称")
                                || StringUtils.equals(key,"归属地市")
                                || StringUtils.equals(key,"归属组别"))){
                            OperationKfqScoreValue temp = new OperationKfqScoreValue();
                            temp.setPkId(baseId);
                            String indexBaseId = indexNameToIdMapping.get(key);
                            temp.setIndexId(indexBaseId);
                            if(StringUtils.isNotBlank(String.valueOf(value))){
                                String valueStr = String.valueOf(value);
                                BigDecimal valueBigDecimal = new BigDecimal(valueStr).setScale(2, RoundingMode.HALF_UP);
                                temp.setiValue(valueBigDecimal.toString());
                            }
                            temp.setBaseCreateBy(SecurityUtils.getUserId());
                            temp.setBaseCreateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateBy(SecurityUtils.getUserId());
                            updateScoreValuesList.add(temp);
                        }
                    }
                }else{
                    OperationKfqInfo operationKfqInfo = new OperationKfqInfo();
                    operationKfqInfo.setBaseId(IdUtil.simpleUUID());
                    operationKfqInfo.setBaseCreateBy(SecurityUtils.getUserId());
                    operationKfqInfo.setBaseCreateTime(DateUtils.getNowDate());
                    operationKfqInfo.setBaseUpdateTime(DateUtils.getNowDate());
                    operationKfqInfo.setBaseUpdateBy(SecurityUtils.getUserId());
                    operationKfqInfo.setYears(years);
                    for(Map.Entry<String,Object> t : item.entrySet()){
                        String key = t.getKey();
                        Object value = t.getValue();
                        if(StringUtils.equals(key,"考核期间")){
                            String khqjStr = DictUtils.getDictValue("kfq_khqj", (String) value);
                            if(StringUtils.isBlank(khqjStr)){
                                errorMsg.append("【" + kfpNameTemp + "】考核期间填写有误<br/>");
                                continue;
                            }
                            operationKfqInfo.setDap(khqjStr);
                        }else if(StringUtils.equals(key,"开发区名称")){
                            String kfqCode = kfqInfoMap.get((String) value);
                            if(StringUtils.isBlank(kfqCode)){
                                errorMsg.append("【" + kfpNameTemp + "】开发区名称填写有误(参考开发区名称模版)<br/>");
                                continue;
                            }
                            operationKfqInfo.setDzn(kfqCode);
                        }else if(StringUtils.equals(key,"归属地市")){
                            String cityName = cityInfoMap.get((String) value);
                            if(StringUtils.isBlank(cityName)){
                                errorMsg.append("【" + kfpNameTemp + "】归属地市填写有误<br/>");
                                continue;
                            }
                            operationKfqInfo.setCity(cityName);
                        }else if(StringUtils.equals(key,"归属组别")){
                            String zbStr = DictUtils.getDictValue("kfq_group", (String) value);
                            if(StringUtils.isBlank(zbStr)){
                                errorMsg.append("【" + kfpNameTemp + "】归属组别填写有误<br/>");
                                continue;
                            }
                            operationKfqInfo.setGroupCode(zbStr);
                        }else{
                            OperationKfqScoreValue temp = new OperationKfqScoreValue();
                            temp.setBaseId(IdUtil.simpleUUID());
                            temp.setPkId(operationKfqInfo.getBaseId());
                            String indexBaseId = indexNameToIdMapping.get(key);
                            temp.setIndexId(indexBaseId);
                            if(StringUtils.isNotBlank(String.valueOf(value))){
                                String valueStr = String.valueOf(value);
                                BigDecimal valueBigDecimal = new BigDecimal(valueStr).setScale(2, RoundingMode.HALF_UP);
                                temp.setiValue(valueBigDecimal.toString());
                            }
                            temp.setBaseCreateBy(SecurityUtils.getUserId());
                            temp.setBaseCreateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateBy(SecurityUtils.getUserId());
                            scoreValuesList.add(temp);
                        }
                    }
                    operationKfqInfo.setCounty(SecurityUtils.getLoginUser().getSysUser().getDistrict());
                    mainInfoTable.add(operationKfqInfo);
                }
            }

            if(StringUtils.isNotBlank(errorMsg.toString())){
                throw new RuntimeException(errorMsg.toString());
            }

            if(!mainInfoTable.isEmpty()){
                operationKfqInfoMapper.batchInsertKfqInfo(mainInfoTable);
                operationKfqScoreValueMapper.batchInsertScore(scoreValuesList);
            }

            if(!updateScoreValuesList.isEmpty()){
                operationKfqScoreValueMapper.batchUpdateValue(updateScoreValuesList);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 读取导入数据的模版
     *
     * @param file
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importExcelTemplateScore(MultipartFile file,String years) {
        try {

            //查询出指标对应的ID
            OperationIndexManager params = new OperationIndexManager();
            params.setYears(years);
            List<OperationIndexManager> operationIndexManagers = operationIndexManagerMapper.selectOperationIndexManagerList(params);

            if(operationIndexManagers == null){
                throw new RuntimeException("没有查询到当年的指标");
            }
            List<KfqInfo> result = operationIndexManagerMapper.loadAllKfqInfo();
            if(result == null || result.isEmpty()){
                throw new RuntimeException("没有找到开发区信息");
            }

            List<KfqInfo> cityArgs = operationIndexManagerMapper.selectAllCity();
            if(cityArgs == null || cityArgs.isEmpty()){
                throw new RuntimeException("没有找到地市信息");
            }
            Map<String, String> cityInfoMap = cityArgs.stream().collect(Collectors.toMap(KfqInfo::getName, KfqInfo::getCode));
            Map<String, String> kfqInfoMap = result.stream().collect(Collectors.toMap(KfqInfo::getName, KfqInfo::getCode));
            Workbook wb = WorkbookFactory.create(file.getInputStream());
            Sheet sheetAt = wb.getSheetAt(0);
            if(sheetAt == null){
                throw new RuntimeException("未读取到数据");
            }
            //读出Sheet的标头
            Row headRow = sheetAt.getRow(0);
            //读出最后一个单元格
            short headLastCellNum = headRow.getLastCellNum();

            //标头的索引对应的标头文字
            Map<String,String> indexToTitleMapping = new HashMap<>(16);

            for(short i = 0;i < headLastCellNum;i++){
                Cell readCell = headRow.getCell((int) i);
                String readCellValue = readCell.getStringCellValue();
                indexToTitleMapping.put(String.valueOf(i),readCellValue);
            }

            List<Map<String,Object>> sroceList = new ArrayList<>();

            //有多少行的数据
            int lastRowNum = sheetAt.getLastRowNum();
            for(int i =1;i<=lastRowNum;i++){
                Row tempRow = sheetAt.getRow(i);
                Map<String,Object> temp = new HashMap<>();
                for(short j = 0;j < headLastCellNum;j++){
                    Cell cell = tempRow.getCell(j);
                    if(cell != null){
                        String cellType = cell.getCellType().name();
                        if(StringUtils.equals(cellType,"STRING")){
                            temp.put(indexToTitleMapping.get(String.valueOf(j)),cell.getStringCellValue());
                        }else if(StringUtils.equals(cellType,"NUMERIC")){
                            temp.put(indexToTitleMapping.get(String.valueOf(j)),cell.getNumericCellValue());
                        }
                    }

                }
                sroceList.add(temp);
            }

            Map<String, String> indexNameToIdMapping = operationIndexManagers.stream().collect(Collectors.toMap(k -> k.getIndexName() + "分数", OperationIndexManager::getBaseId));

            //查询当前年份导入的开发区信息

            if(sroceList.isEmpty()){
                throw new RuntimeException("没有找到文件中的数据");
            }
            Map<String, Object> xlsxZeroData = sroceList.get(0);

            //查询当前年份导入的开发区信息
            String khqjSt = (String)xlsxZeroData.get("考核期间");
            String gszbSt = (String)xlsxZeroData.get("归属组别");

            String khqjZd = DictUtils.getDictValue("kfq_khqj", (String) khqjSt);
            String zbZd = DictUtils.getDictValue("kfq_group", (String) gszbSt);

            OperationKfqInfo kfqInfoParams = new OperationKfqInfo();
            kfqInfoParams.setYears(years);
            kfqInfoParams.setGroupCode(zbZd);
            kfqInfoParams.setDap(khqjZd);
            List<OperationKfqInfo> operationKfqInfos = operationKfqInfoMapper.selectOperationKfqInfoList(kfqInfoParams);
            Map<String, String> existKfq = new HashMap<>();
            if(operationKfqInfos != null && !operationKfqInfos.isEmpty()){
                existKfq = operationKfqInfos.stream().collect(Collectors.toMap(OperationKfqInfo::getDzn, OperationKfqInfo::getBaseId));
            }

            //构建保存到数据库中的数据格式
            List<OperationKfqInfo> mainInfoTable = new ArrayList<>();
            List<OperationKfqScoreValue> scoreValuesList = new ArrayList<>();
            List<OperationKfqScoreValue> updateScoreValuesList = new ArrayList<>();

            StringBuilder errorMsg = new StringBuilder();

            for(Map<String,Object> item : sroceList){
                String kfpNameTemp = (String)item.get("开发区名称");
                if(existKfq.containsKey(kfpNameTemp)){
                    String baseId = existKfq.get(kfpNameTemp);
                    for(Map.Entry<String,Object> t : item.entrySet()){
                        String key = t.getKey();
                        Object value = t.getValue();
                        if(!(StringUtils.equals(key,"考核期间")
                                || StringUtils.equals(key,"开发区名称")
                                || StringUtils.equals(key,"归属地市")
                                || StringUtils.equals(key,"归属组别"))){
                            OperationKfqScoreValue temp = new OperationKfqScoreValue();
                            temp.setPkId(baseId);
                            String indexBaseId = indexNameToIdMapping.get(key);
                            temp.setIndexId(indexBaseId);
                            if(StringUtils.isNotBlank(String.valueOf(value))){
                                String valueStr = String.valueOf(value);
                                BigDecimal valueBigDecimal = new BigDecimal(valueStr).setScale(2, RoundingMode.HALF_UP);
                                temp.setScore(valueBigDecimal.toString());
                            }
                            temp.setBaseCreateBy(SecurityUtils.getUserId());
                            temp.setBaseCreateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateBy(SecurityUtils.getUserId());
                            updateScoreValuesList.add(temp);
                        }
                    }
                }else{
                    OperationKfqInfo operationKfqInfo = new OperationKfqInfo();
                    operationKfqInfo.setBaseId(IdUtil.simpleUUID());
                    operationKfqInfo.setBaseCreateBy(SecurityUtils.getUserId());
                    operationKfqInfo.setBaseCreateTime(DateUtils.getNowDate());
                    operationKfqInfo.setBaseUpdateTime(DateUtils.getNowDate());
                    operationKfqInfo.setBaseUpdateBy(SecurityUtils.getUserId());
                    operationKfqInfo.setYears(years);

                    for(Map.Entry<String,Object> t : item.entrySet()){
                        String key = t.getKey();
                        Object value = t.getValue();
                        if(StringUtils.equals(key,"考核期间")){
                            String valueStr = (String) value;
                            valueStr = StringUtils.trim(valueStr);
                            String khqjStr = DictUtils.getDictValue("kfq_khqj", valueStr);
                            if(StringUtils.isBlank(khqjStr)){
                                errorMsg.append("【" + kfpNameTemp + "】考核期间填写有误").append("<br/>");
                                continue;
                            }
                            operationKfqInfo.setDap(khqjStr);
                        }else if(StringUtils.equals(key,"开发区名称")){
                            String valueStr = (String) value;
                            valueStr = StringUtils.trim(valueStr);
                            String kfqCode = kfqInfoMap.get((String) valueStr);
                            if(StringUtils.isBlank(kfqCode)){
                                errorMsg.append("【" + kfpNameTemp + "】开发区名称填写有误").append("<br/>");
                                continue;
                            }
                            operationKfqInfo.setDzn(kfqCode);
                        }else if(StringUtils.equals(key,"归属地市")){
                            String valueStr = (String) value;
                            valueStr = StringUtils.trim(valueStr);
                            String cityName = cityInfoMap.get((String) valueStr);
                            if(StringUtils.isBlank(cityName)){
                                errorMsg.append("【" + kfpNameTemp + "】归属地市填写有误").append("<br/>");
                                continue;
                            }
                            operationKfqInfo.setCity(cityName);
                        }else if(StringUtils.equals(key,"归属组别")){
                            String valueStr = (String) value;
                            valueStr = StringUtils.trim(valueStr);
                            String zbStr = DictUtils.getDictValue("kfq_group", valueStr);
                            if(StringUtils.isBlank(zbStr)){
                                errorMsg.append("【" + kfpNameTemp + "】归属组别填写有误").append("<br/>");
                                continue;
                            }
                            operationKfqInfo.setGroupCode(zbStr);
                        }else if(StringUtils.equals(key,"加分项")){
                            String valueStr = (String) value;
                            operationKfqInfo.setAddScoreItem(valueStr);
                        }else if(StringUtils.equals(key,"加分项分值")){
                            String valueStr = String.valueOf(value);
                            operationKfqInfo.setAddScoreValue(valueStr);
                        }else if(StringUtils.endsWith(key,"分数")){
                            OperationKfqScoreValue temp = new OperationKfqScoreValue();
                            temp.setBaseId(IdUtil.simpleUUID());
                            temp.setPkId(operationKfqInfo.getBaseId());
                            temp.setBaseCreateBy(SecurityUtils.getUserId());
                            temp.setBaseCreateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateTime(DateUtils.getNowDate());
                            temp.setBaseUpdateBy(SecurityUtils.getUserId());
                            String indexBaseId = indexNameToIdMapping.get(key);
                            temp.setIndexId(indexBaseId);
                            if(StringUtils.isNotBlank(String.valueOf(value))){
                                String valueStr = String.valueOf(value);
                                BigDecimal valueBigDecimal = new BigDecimal(valueStr).setScale(2, RoundingMode.HALF_UP);
                                temp.setScore(valueBigDecimal.toString());
                            }

                            //获取出排名
                            String str = key.substring(0, key.length() - 2) + "排名";
                            String o = String.valueOf(item.get(str));
                            o = o.replace(".0","");
                            temp.setGroupOrder(o);
                            scoreValuesList.add(temp);
                        }
                    }
                    mainInfoTable.add(operationKfqInfo);
                }
            }

            if(StringUtils.isNotBlank(errorMsg.toString())){
                throw new RuntimeException(errorMsg.toString());
            }

            if(!updateScoreValuesList.isEmpty()){
                operationKfqScoreValueMapper.batchUpdateScore(updateScoreValuesList);
            }

            if(!mainInfoTable.isEmpty()){
                operationKfqInfoMapper.batchInsertKfqInfo(mainInfoTable);
                operationKfqScoreValueMapper.batchInsertScore(scoreValuesList);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }



    @Override
    public AjaxResult queryCharts(ChartsParams chartsParams) {
        List<ChartsDbData> chartsDbData = operationKfqInfoMapper.queryCharts(chartsParams);

        if(chartsDbData != null && !chartsDbData.isEmpty()){
            List<ChartsDbData> valueOrderList = chartsDbData.stream().filter(k -> k.getiValue() != null).sorted(Comparator.comparing(ChartsDbData::getiValue).reversed()).collect(Collectors.toList());
            List<ChartsDbData> scoreOrderList = chartsDbData.stream().filter(k -> k.getScore() != null).sorted(Comparator.comparing(ChartsDbData::getScore).reversed()).collect(Collectors.toList());


            Map<String,Object> result = new HashMap<>();
            result.put("value",valueOrderList);
            result.put("score",scoreOrderList);
            return AjaxResult.success(result);
        }

        Map<String,Object> result = new HashMap<>();
        result.put("value",new ArrayList<>());
        result.put("score",new ArrayList<>());
        return AjaxResult.success(result);
    }

    @Override
    public AjaxResult loadKfpData() {
        List<KfqInfo> resut = operationKfqInfoMapper.loadKfpData();
        return AjaxResult.success(resut);
    }

    /**
     * 导出开发区数据
     */
    @Override
    public Workbook createExportDataWb(OperationKfqInfo params) {

        OperationIndexManager indexManParams = new OperationIndexManager();
        indexManParams.setYears(params.getYears());
        List<OperationIndexManager> operationIndexManagers = operationIndexManagerMapper.selectOperationIndexManagerList(indexManParams);
        Workbook wb = new SXSSFWorkbook(500);
        XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle();
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setWrapText(true);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(180,198,231)));
        SXSSFSheet sheet = ((SXSSFWorkbook) wb).createSheet("开发区信息");

        //创建标头行
        SXSSFRow row = sheet.createRow(0);

        //创建固定的单元格
        SXSSFCell yearCell = row.createCell(0, CellType.STRING);
        SXSSFCell khqjCell = row.createCell(1, CellType.STRING);
        SXSSFCell kfqNameCell = row.createCell(2, CellType.STRING);
        SXSSFCell cityCell = row.createCell(3, CellType.STRING);
        SXSSFCell groupCell = row.createCell(4, CellType.STRING);
        SXSSFCell zfCell = row.createCell(5, CellType.STRING);

        yearCell.setCellStyle(headerStyle);
        khqjCell.setCellStyle(headerStyle);
        kfqNameCell.setCellStyle(headerStyle);
        cityCell.setCellStyle(headerStyle);
        groupCell.setCellStyle(headerStyle);
        zfCell.setCellStyle(headerStyle);

        yearCell.setCellValue("年度");
        khqjCell.setCellValue("考核期间");
        kfqNameCell.setCellValue("开发区名称");
        cityCell.setCellValue("归属地市");
        groupCell.setCellValue("归属组别");
        zfCell.setCellValue("总分");

        for(int i = 0;i< operationIndexManagers.size();i++){
            OperationIndexManager operationIndexManager = operationIndexManagers.get(i);
            String indexName = operationIndexManager.getIndexName();
            SXSSFCell tempValueCell = row.createCell(i + 6 + i, CellType.STRING);
            SXSSFCell tempScoreCell = row.createCell(i + 7 + i, CellType.STRING);
            tempValueCell.setCellStyle(headerStyle);
            tempScoreCell.setCellStyle(headerStyle);
            tempValueCell.setCellValue(indexName + "分值");
            tempScoreCell.setCellValue(indexName + "分数");
        }
        short lastCellNum = row.getLastCellNum();
        for(short j = 0; j <lastCellNum; j++){
            sheet.setColumnWidth((int)j,500*11);
        }

        List<OperationKfqInfo> operationKfqInfos = operationKfqInfoMapper.selectOperationKfqInfoListExportData(params);
        if(operationKfqInfos != null && !operationKfqInfos.isEmpty()){
            for(OperationKfqInfo temp : operationKfqInfos){
                temp.setCity(redisTemplate.opsForValue().get("area:" + temp.getCity()));
                temp.setGroupCode(DictUtils.getDictLabel("kfq_group",temp.getGroupCode()));
                temp.setDap(DictUtils.getDictLabel("kfq_khqj",temp.getDap()));
                List<OperationKfqScoreValue> scoreValues = temp.getScoreValues();
                if(scoreValues != null && !scoreValues.isEmpty()){
                    List<OperationKfqScoreValue> collect = scoreValues.stream().sorted(Comparator.comparing(OperationKfqScoreValue::getBaseCreateTime)).collect(Collectors.toList());
                    if(collect != null && !collect.isEmpty()){
                        BigDecimal bigDecimal = collect.stream().map(k -> StringUtils.isBlank(k.getScore()) ? BigDecimal.ZERO : new BigDecimal(k.getScore())).reduce(BigDecimal::add).get();
                        temp.setZf(bigDecimal);
                    }else{
                        temp.setZf(BigDecimal.ZERO);
                    }
                    temp.setScoreValues(collect);
                }
            }


            for(int i = 0;i < operationKfqInfos.size(); i++){
                OperationKfqInfo operationKfqInfo = operationKfqInfos.get(i);

                SXSSFRow tempRow = sheet.createRow(i + 1);
                SXSSFCell yearTempCell = tempRow.createCell(0, CellType.STRING);
                SXSSFCell khqjTempCell = tempRow.createCell(1, CellType.STRING);
                SXSSFCell kfqNameTempCell = tempRow.createCell(2, CellType.STRING);
                SXSSFCell cityTempCell = tempRow.createCell(3, CellType.STRING);
                SXSSFCell groupTempCell = tempRow.createCell(4, CellType.STRING);
                SXSSFCell zfTempCell = tempRow.createCell(5, CellType.NUMERIC);

                yearTempCell.setCellValue(operationKfqInfo.getYears());
                khqjTempCell.setCellValue(operationKfqInfo.getDap());
                kfqNameTempCell.setCellValue(operationKfqInfo.getDzn());
                cityTempCell.setCellValue(operationKfqInfo.getCity());
                groupTempCell.setCellValue(operationKfqInfo.getGroupCode());
                zfTempCell.setCellValue(operationKfqInfo.getZf().doubleValue());

                int cellIndex = 6;
                List<OperationKfqScoreValue> scoreValues = operationKfqInfo.getScoreValues();
                for(OperationKfqScoreValue pp : scoreValues){
                    SXSSFCell tempValueCell = tempRow.createCell(cellIndex, CellType.STRING);
                    SXSSFCell tempScoreCell = tempRow.createCell(cellIndex + 1, CellType.STRING);

                    tempValueCell.setCellValue(pp.getiValue());
                    tempScoreCell.setCellValue(pp.getScore());
                    cellIndex = cellIndex + 2;
                }
            }
        }
        return wb;
    }
}